package cn.edu.sdjzu.xg.dao;

import cn.edu.sdjzu.xg.domain.Department;
import cn.edu.sdjzu.xg.domain.Teacher;
import cn.edu.sdjzu.xg.exception.BysjException;
import cn.edu.sdjzu.xg.service.DepartmentService;
import cn.edu.sdjzu.xg.util.Condition;
import cn.edu.sdjzu.xg.util.JdbcHelper;
import cn.edu.sdjzu.xg.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.TreeSet;

public class TeacherDao {
    private static TeacherDao teacherDao =new TeacherDao();
    private TeacherDao(){

    }
    public static TeacherDao getInstance(){return  teacherDao;}
    public  boolean update(Teacher teacher) throws SQLException{
        Connection connection = JdbcHelper.getConn();
        String updateTeacher_sql ="updater teacher set name =?,department_id=? where id=?";
        PreparedStatement preparedStatement =connection.prepareStatement(updateTeacher_sql);
        preparedStatement.setString(1,teacher.getName());
        preparedStatement.setInt(2,teacher.getDepartment().getId());
        preparedStatement.setInt(3,teacher.getId());
        int affectedRowNum =preparedStatement.executeUpdate();
        System.out.println("修改了"+affectedRowNum+"条记录");
        JdbcHelper.close(preparedStatement,connection);
        return affectedRowNum>0;

    }
    public boolean add (Teacher teacher)throws SQLException {
        Connection connection= JdbcHelper.getConn();
        StringBuilder sql_insert = new StringBuilder("insert into teacher(name,school_id)values"+
                "(?,?)");
        PreparedStatement pstmt_add=connection.prepareStatement(sql_insert.toString());
        pstmt_add.setString(1, teacher.getName());
        pstmt_add.setInt(3, teacher.getDepartment().getId());
        int affectedRowNum =pstmt_add.executeUpdate();
        JdbcHelper.close(pstmt_add,connection);
        return affectedRowNum>0;
    }
    public boolean delete(Integer id)throws SQLException{
        Connection connection = JdbcHelper.getConn();
        String sql_delete = "delete from teacher where id = ?";
        PreparedStatement pstmt_delete = connection.prepareStatement(sql_delete);
        pstmt_delete.setInt(1, id);
        int rowAffected = pstmt_delete.executeUpdate();
        JdbcHelper.close(pstmt_delete,connection );
        return rowAffected>0;
    }
    public Teacher find(Connection connection, Integer id) throws SQLException {
        Teacher desiredTeacher = null;
        String sql_select = "select * from teacher where id =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select);
        preparedStatement.setInt(1,id);

        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {

            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            Integer department_id = resultSet.getInt("school_id");
            Department department= DepartmentService.getInstance().find(department_id);
            desiredTeacher = new Teacher(id,name, department);

        }
        JdbcHelper.close(resultSet,preparedStatement,connection);
        return desiredTeacher;
    }
    public Collection<Teacher> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException, BysjException {
        Collection<Teacher>desiredTeachers=new TreeSet<>();
        Teacher desiredTeacher = null;
        StringBuilder sql_select = new StringBuilder("select * from teacher");
        if (conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
            System.out.println(sql_select);
        }
        int totalNum = CommonDao.getInstance().count(connection,"Teacher",conditionList);
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);

        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            Integer id = resultSet.getInt("id");
            String name =resultSet.getString("name");
            Integer department_id = resultSet.getInt("school_id");
            Department department= DepartmentService.getInstance().find(department_id);
            desiredTeacher = new Teacher(id,name, department);
            desiredTeachers.add(desiredTeacher);


        }
        JdbcHelper.close(resultSet,preparedStatement,connection);
        return desiredTeachers;
    }


}
